package com.jxc.jxc01.yk.dao;

import com.jxc.jxc01.yk.entity.PageHelper;
import com.jxc.jxc01.yk.entity.Sales;
import com.jxc.jxc01.yk.util.JDBCConn;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SalesDaoImpl implements ISalesDao {
    /**
     * 增加销售清单
     */
    public boolean addSales(List<Sales> sales) {
        ResultSet rs = null;
        Sales sales1 = new Sales();
        System.out.println("进入添加Dao层");
        try {
            //3.查询销售表
            String sql = "insert into sales(salesnumber,salesperson,salesgoods,variety,salesname," +
                    "units,salesvolume,salesprice,totalprice,salestime,status) values(?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            int rows = 0;
            for (Sales sale : sales) {
                //sales1 = sale;
                System.out.println(sale.toString());
                pstmt.setInt(1, sale.getSalesnumber());
                pstmt.setString(2, sale.getSalesperson());
                pstmt.setString(3, sale.getSalesgoods());
                pstmt.setString(4, sale.getVariety());
                pstmt.setString(5, sale.getSalesname());
                pstmt.setString(6, sale.getUnits());
                pstmt.setInt(7, sale.getSalesvolume());
                pstmt.setDouble(8, sale.getSalesprice());
                pstmt.setDouble(9, sale.getTotalprice());
                pstmt.setString(10, sale.getSalestime());
                pstmt.setString(11, sale.getCondition());
                rows = pstmt.executeUpdate();
            }
            if (rows == 0) {
                //添加失败
                System.out.println("添加失败");
                return false;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }


    /**
     * 销售商品清单
     */
    public PageHelper<Sales> findSalesList(PageHelper<Sales> pageHelper) {
        //将查询到的销售清单信息存储集合中
        List<Sales> sales = new ArrayList<>();
        ResultSet rs = null;
        try {
            //分页查询
            String sql = "select count(*) total from sales";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            rs = pstmt.executeQuery();
            //列表数据总数
            while (rs.next()){
                int total = rs.getInt("total");
                pageHelper.setTotal(total);
            }
            //总页数
            pageHelper.setPageCount(pageHelper.getTotal()%pageHelper.getPageSize()==0?
                    pageHelper.getTotal()/pageHelper.getPageSize():pageHelper.getTotal()/pageHelper.getPageSize()+1);
            //3.查询销售表
            sql = "select * from sales limit ?,?";
             pstmt = JDBCConn.conn(sql);
            pstmt.setInt(1, (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            pstmt.setInt(2, pageHelper.getPageSize());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Sales sale = new Sales();
                sale.setId(rs.getInt(1));
                sale.setSalesnumber(rs.getInt(2));
                sale.setSalesperson(rs.getString(3));
                sale.setSalesgoods(rs.getString(4));
                sale.setVariety(rs.getString(5));
                sale.setSalesname(rs.getString(6));
                sale.setUnits(rs.getString(7));
                sale.setSalesvolume(rs.getInt(8));
                sale.setSalesprice(rs.getDouble(9));
                sale.setTotalprice(rs.getDouble(10));
                sale.setSalestime(rs.getString(11));
                sale.setCondition(rs.getString(12));
                sales.add(sale);
            }

        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }
        pageHelper.setData(sales);
        return pageHelper;
    }

    /**
     * 根据id查找某一条记录
     */
    public List<Sales> findSalesId(int id) {
        //将查询到的销售清单信息存储集合中
        List<Sales> sales = new ArrayList<>();
        ResultSet rs = null;
        try {

            //3.查询销售表
            String sql = "select * from sales where id=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Sales sale = new Sales();
                sale.setId(rs.getInt(1));
                sale.setSalesnumber(rs.getInt(2));
                sale.setSalesperson(rs.getString(3));
                sale.setSalesgoods(rs.getString(4));
                sale.setVariety(rs.getString(5));
                sale.setSalesname(rs.getString(6));
                sale.setUnits(rs.getString(7));
                sale.setSalesvolume(rs.getInt(8));
                sale.setSalesprice(rs.getDouble(9));
                sale.setTotalprice(rs.getDouble(10));
                sale.setSalestime(rs.getString(11));
                sale.setCondition(rs.getString(12));
                sales.add(sale);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return sales;
    }

    /**
     * 根据销售客户查找某一条记录
     */
    public List<Sales> findSalesName(String salesname) {
        //将查询到的销售清单信息存储集合中
        List<Sales> sales = new ArrayList<>();
        ResultSet rs = null;
        try {
            String sql = "select count(*) total from sales where salesname like ?";
            String name = "%"+salesname+"%";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int total = rs.getInt(1);
                System.out.println(total);
            }
            sql = "select * from sales where salesname like ?";
            pstmt = JDBCConn.conn(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Sales sale = new Sales();
                sale.setId(rs.getInt(1));
                sale.setSalesnumber(rs.getInt(2));
                sale.setSalesperson(rs.getString(3));
                sale.setSalesgoods(rs.getString(4));
                sale.setVariety(rs.getString(5));
                sale.setSalesname(rs.getString(6));
                sale.setUnits(rs.getString(7));
                sale.setSalesvolume(rs.getInt(8));
                sale.setSalesprice(rs.getDouble(9));
                sale.setTotalprice(rs.getDouble(10));
                sale.setSalestime(rs.getString(11));
                sale.setCondition(rs.getString(12));
                sales.add(sale);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sales;
    }

    /**根据销售单号查找某一条记录*/
    public List<Sales> findSalesSalesNumber(int salesnumber){
//将查询到的销售清单信息存储集合中
        List<Sales> sales = new ArrayList<>();
        ResultSet rs = null;
        try {
            //3.查询销售表
            String sql = "select * from sales where salesnumber=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setInt(1, salesnumber);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Sales sale = new Sales();
                sale.setId(rs.getInt(1));
                sale.setSalesnumber(rs.getInt(2));
                sale.setSalesperson(rs.getString(3));
                sale.setSalesgoods(rs.getString(4));
                sale.setVariety(rs.getString(5));
                sale.setSalesname(rs.getString(6));
                sale.setUnits(rs.getString(7));
                sale.setSalesvolume(rs.getInt(8));
                sale.setSalesprice(rs.getDouble(9));
                sale.setTotalprice(rs.getDouble(10));
                sale.setSalestime(rs.getString(11));
                sale.setCondition(rs.getString(12));
                sales.add(sale);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return sales;
    }

    /**
     * 编辑,删除销售清单
     */
    public boolean deleteSales(int id) {
        ResultSet rs = null;
        try {
            //3.查询销售表
            String sql = "delete from sales where id=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setInt(1, id);
            int rows = pstmt.executeUpdate();
            if (rows == 0) {
                //删除失败
                return false;
            }
        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return true;
    }

    /**
     * 修改销售清单
     */
    public boolean updateSales(Sales sale) {
        ResultSet rs = null;
        try {

            //3.查询销售表
            String sql = "update sales set salesperson=?,salesgoods=?," +
                    "variety=?,salesname=?,units=?,salesvolume=?,salesprice=?,totalprice=?," +
                    "salestime=?,status=?  where id=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            //pstmt.setInt(1, sale.getSalesnumber());
            pstmt.setString(1, sale.getSalesperson());
            pstmt.setString(2, sale.getSalesgoods());
            pstmt.setString(3, sale.getVariety());
            pstmt.setString(4, sale.getSalesname());
            pstmt.setString(5, sale.getUnits());
            pstmt.setInt(6, sale.getSalesvolume());
            pstmt.setDouble(7, sale.getSalesprice());
            pstmt.setDouble(8, sale.getTotalprice());
            pstmt.setString(9, sale.getSalestime());
            pstmt.setString(10, sale.getCondition());
            pstmt.setInt(11, sale.getId());
            int rows = pstmt.executeUpdate();
            if (rows == 0) {
                //修改失败
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return true;
    }
}
